Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Query tuning

How you structure a query determines how efficiently you access a database. Using your MSS data source efficiently enhances the performance of DataServer applications. The standard approach to enhancement is using selection criteria to refine access to data, but you can further optimize the DataServer’s execution of a query by specifying the Progress QUERY–TUNING phrase.

You can include the QUERY–TUNING phrase in these Progress statements:

FOR EACH

Syntax

FOR EACH table QUERY-TUNING(query-tuning-option query-tuning-option...)

OPEN QUERY

Syntax

OPEN QUERY query QUERY-TUNING(query-tuning-option query-tuning-option...)

DO PRESELECT

Syntax

DO PRESELECT table QUERY-TUNING(query-tuning-option query-tuning-option...)

REPEAT PRESELECT

Syntax

REPEAT PRESELECT table QUERY-TUNING(query-tuning-option query-tuning-option...)

You must place the QUERY–TUNING phrase after the last record phrase. For example, place it near the end of the statement where you also place block modifier phrases such as BREAK, ON ERROR, and TRANSACTION.

You can include multiple query-tuning options in a single statement; simply separate each option from the previous one by a single space.

Table 4–1 describes the query-tuning options.

Table 4–1: Query-tuning options

Option	Description
ARRAY–MESSAGE NO–ARRAY–MESSAGE	Specifies whether the DataServer sends multiple result rows in a single logical network message, thereby reducing network traffic. Default: ARRAY–MESSAGE, if the query uses a lookahead cursor.
CACHE–SIZE integer	Specifies the size in bytes of the cache used by lookahead cursors. A larger cache size can improve performance for queries that return a large number of records because the DataServer might need fewer SQL statements to get the results. This value will override a cache size specified with -Dsrv QT_CACHE_SIZE. Minimum: The DataServer always caches at least one record. Maximum: None. Default: 30000.
DEBUG EXTENDED DEBUG SQL NO–DEBUG	Specifies whether the DataServer should print to the dataserv.lg file the debugging information that it generates for a query. Specify DEBUG SQL to print only the SQL that the DataServer executes against the ODBC data source. Specify DEBUG EXTENDED to print additional information, such as cursor statistics. Specify DEBUG option to override the NO–DEBUG default. Default: NO–DEBUG.
JOIN–BY–SQLDB NO–JOIN–BY–SQLDB	Specifies whether the DataServer allows its data source to perform a join (this usually improves performance). JOIN–BY–SQLDB implies SEPARATE–CONNECTION queries that include joins. Default: JOIN–BY–SQLDB. JOIN–BY–SQLDB is a compile-time option. A query must be compiled to use or not use this option. You can turn off the JOIN–BY–SQLDB default globally at compile time by specifying the Server Join (-nojoinbysqldb) startup parameter when you start an OpenEdge session. This parameter does not override the explicit use of JOIN–BY–SQLDB in the QUERY–TUNING phrase.
LOOKAHEAD NO–LOOKAHEAD	Specifies whether the DataServer uses lookahead or standard cursors. Lookahead cursors fetch as many records as fit in the allocated cache (see the CACHE–SIZE entry in this table). This reduces the number of SQL statements and network messages that are required, thereby improving performance. Using lookahead cursors results in behavior that is different from an OpenEdge database because changes made to the records in the cache might not be immediately visible. Specify NO–LOOKAHEAD for behavior that is consistent with OpenEdge. Default: LOOKAHEAD, when statements use NO-LOCK or when statements use SHARE-LOCK with TXN_ISOLATION level set to 1 (read uncommitted.)
SEPARATE–CONNECTION NO–SEPARATE–CONNECTION	Specifies whether each cursor should use a separate database connection. Executing cursors in separate connections might improve performance because the DataServer does not have to restart the cursors and sort the results. Do not specify SEPARATE–CONNECTION if you require behavior that is consistent with OpenEdge. Default: NO–SEPARATE–CONNECTION except in certain cases. For details, see the "Managing connections to a MS SQL Server database" section.

All but two of the QUERY–TUNING options take effect at both compile time and run time. The exceptions are JOIN–BY–SQLDB and NO–JOIN–BY–SQLDB, which apply only at compile time. You can override query-tuning defaults (except JOIN–BY–SQLDB) at run-time by specifying the appropriate startup parameters.

The following example shows how to use the QUERY–TUNING phrase to enhance performance. It includes a join, JOIN-BY-SQLDB, that the DataServer instructs the MSS data source to perform by default, as shown:

FOR EACH customer, EACH order OF customer WHERE order-num > 20 BY customer.cust-num QUERY-TUNING (JOIN-BY-SQLDB)

The QUERY–TUNING options in this example specify the following:

Lookahead cursors are not used (the NO–LOOKAHEAD option).
The DataServer writes an extended report on the SQL statements that it executes (the DEBUG EXTENDED option).

When the DataServer constructs queries for an MSS data source, it uses the QUERY–TUNING options that you specify as guidelines. This is because there might be syntax considerations that prevent the DataServer from applying the QUERY–TUNING options as specified. In such a case, the DataServer executes the query using the most appropriate options.

Note: The DataServer does not issue errors or warnings if it does not apply the QUERY–TUNING options that you specify.